﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using Teradata.Client.Provider;

using System.Data;

using System.IO;

using System.Web.Security;

using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public partial class reports_ServiceOrderPull : System.Web.UI.Page
{

    protected String ConnString = ConfigurationManager.ConnectionStrings["EDW_GROUP_ID"].ConnectionString;
    //protected String SQLP = System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath("..\\SQL\\ServiceOrderPull.sql"));

    protected String FilterString = String.Empty;
    protected String lblFor = String.Empty;
    protected String lblProducts = String.Empty;

    protected void SalesData_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.CommandTimeout = 0;
    }

    protected void Page_Load(object sender, EventArgs e)
    {

        if (!Page.IsPostBack)
        {
            StartDate.Text = DateTime.Today.Month.ToString() + "/1/" + DateTime.Today.Year.ToString();
            DateTime dtServerSales = Convert.ToDateTime(System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath("..\\SQL\\Data\\ServerSalesDate.txt")));
            EndDate.Text = dtServerSales.ToShortDateString();



        }
    }

    protected void FilterBy_SelectedIndexChanged(object sender, EventArgs e)
    {
        switch (FilterBy.SelectedItem.ToString())
        {
            case "Area Manager":
                Literal1.Visible = true;
                FilterData.DataSource = (new AreaManagers()).Menu;
                FilterData.DataBind();
                FilterData.Visible = true;
                //SalesData.SelectCommand = "SELECT AREA_MGR MGR FROM MIS.ACC_BASE_DATA GROUP BY 1 ORDER BY 1";
                break;
            case "Account Manager":
                Literal1.Visible = true;
                FilterData.DataSource = (new AcctManagers()).Menu;
                FilterData.DataBind();
                FilterData.Visible = true;
                //SalesData.SelectCommand = "SELECT ACCT_MGR MGR FROM MIS.ACC_BASE_DATA GROUP BY 1 ORDER BY 1";
                break;
            default:
                Literal1.Visible = false;
                FilterData.Visible = false;
                //SalesData.SelectCommand = "SELECT AREA_MGR MGR FROM MIS.ACC_BASE_DATA GROUP BY 1 ORDER BY 1";
                break;
        }
        //SalesData.DataBind();
    }

    protected void GetData_Click(object sender, EventArgs e)
    {
        if (StartDate.Text == String.Empty) StartDate.Text = "01/01/2009";
        if (EndDate.Text == String.Empty) EndDate.Text = DateTime.Today.ToShortDateString();

        switch (FilterBy.SelectedItem.ToString())
        {
            case "Area Manager":
                FilterString = "AREA_MGR IN (" + GetFilterList() + ")";
                break;
            case "Account Manager":
                FilterString = "ACCT_MGR IN (" + GetFilterList() + ")";
                break;
            default:
                FilterString = String.Empty;
                break;
        }


        FilterString += GetProductList();
        //Response.ContentType = "application/vnd.ms-excel";

        PullData();
        Image1.Visible = true;
        //Response.End();
    }

    protected String GetFilterList()
    {
        String RetStr = String.Empty;
        List<String> FilterListing = new List<string>();

        for (Int16 ctr = 0; ctr < FilterData.Items.Count; ctr++)
        {
            if (FilterData.Items[ctr].Selected)
                FilterListing.Add(FilterData.Items[ctr].Text.Trim());
        }
        RetStr = "'" + String.Join("','", FilterListing.ToArray()) + "'";

        lblFor = String.Join(" & ", FilterListing.ToArray());
        if (lblFor.Length > 0) lblFor = "for " + lblFor;
        return RetStr;
    }

    protected string GetProductList()
    {
        String RetStr = String.Empty;
        List<String> ProductListing = new List<string>();

        for (int i = 0; i < chlProducts.Items.Count; i++)
            if (chlProducts.Items[i].Selected)
                ProductListing.Add(chlProducts.Items[i].Text);

        if (ProductListing.Count > 0 && ProductListing.Count < 3)
        {
            RetStr = (FilterString.Length > 0 ? "  AND " : "") + "PRODUCT IN (" + "'" + String.Join("','", ProductListing.ToArray()) + "'" + ")";
            lblProducts = String.Join(" and ", ProductListing.ToArray());
        }
        else
        {
            RetStr = "";
            lblProducts = "";
        }

        return RetStr;
    }

    protected void PullData()
    {
        DateTime SD, ED;

        if (Request.Form["StartDate"] == String.Empty)
            SD = new DateTime(2009, 1, 1);
        else
            SD = Convert.ToDateTime(Request.Form["StartDate"]);
        if (Request.Form["EndDate"] == String.Empty)
            ED = DateTime.Today;
        else
            ED = Convert.ToDateTime(Request.Form["EndDate"]);

        Label1.Text = string.Format("{0} Service Orders {1} from {2} through {3}", lblProducts, lblFor, SD.ToShortDateString().Replace("/", "-"), ED.ToShortDateString().Replace("/", "-"));
        FilterString += (FilterString.Length > 0 ? " AND " : "") + string.Format("START_DT >= '{0}' AND START_DT <= '{1}' ", SD.ToShortDateString(), ED.ToShortDateString());
        ServiceOrders so = new ServiceOrders();
        this.GridView1.DataSource = so.GetOrders(FilterString);
        this.GridView1.DataBind();

        Image1.Visible = true;
        Label1.Visible = true;
        pnl.Visible = true;
    }

    protected void imgbtnExport_Click(object sender, ImageClickEventArgs e)
    {
        Export("SalesOrderPull.xls", GridView1);
        Image1.Visible = false;

    }

    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }
}